/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Pais;
/**
 * @author Administrador
 */
public class AdminPais implements PaisManager{
    private Connection c;

    public AdminPais(Connection c){
        this.c = c;
    }
    
    @Override
    public int getIdPais() throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(max(IDPAIS)+1,1) AS ID FROM PAIS";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminPais -> getIdPais() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }

    @Override
    public void savePais(Pais p) throws DataException {
        PreparedStatement preSt = null;
        String query = "";
        try{
            query="INSERT INTO PAIS VALUES("+p.getIdPais();
            query+= ", '"+p.getNombre().toUpperCase();
            query+= "', '"+p.getSigla().toUpperCase()+"',1,0)";            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException("Error savePais(Pais p) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public List<Pais> getPaises() throws DataException{
        List<Pais> paises = new ArrayList<Pais>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDPAIS, NOMBRE, SIGLA";
            query += " FROM PAIS";
            query += " WHERE ACTIVO = 1";
            query += " ORDER BY NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Pais p = new Pais();
                p.setIdPais(rs.getInt("IDPAIS"));
                p.setNombre(rs.getString("NOMBRE"));
                p.setSigla(rs.getString("SIGLA"));
                paises.add(p);
            }
        }catch(SQLException e){
            throw new DataException(" AdminPais -> getPaises() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return paises;
    }
    
    public List<Pais> getPaisesDadosDeBaja() throws DataException{
        List<Pais> paises = new ArrayList<Pais>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDPAIS, NOMBRE, SIGLA";
            query += " FROM PAIS";
            query += " WHERE ACTIVO = 0";
            query += " ORDER BY NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Pais p = new Pais();
                p.setIdPais(rs.getInt("IDPAIS"));
                p.setNombre(rs.getString("NOMBRE"));
                p.setSigla(rs.getString("SIGLA"));
                paises.add(p);
            }
        }catch(SQLException e){
            throw new DataException(" AdminPais -> getPaises() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return paises;
    }
    
    public Pais getPais(int idPais) throws DataException{
        Pais p = new Pais();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDPAIS, NOMBRE, SIGLA, ACTIVO";
            query += " FROM PAIS";
            query += " WHERE IDPAIS = "+idPais;            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                p.setIdPais(rs.getInt("IDPAIS"));
                p.setNombre(rs.getString("NOMBRE"));
                p.setSigla(rs.getString("SIGLA"));
                p.setActivo(rs.getBoolean("ACTIVO"));
            }
        }catch(SQLException e){
            throw new DataException(" AdminPais -> getPais(int idPais) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return p;
    }
    
    public Pais getPaisDefault() throws DataException{
        Pais p = new Pais();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDPAIS, NOMBRE, SIGLA, ACTIVO";
            query += " FROM PAIS";
            query += " WHERE DEFAULT_P = 1";             
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                p.setIdPais(rs.getInt("IDPAIS"));
                p.setNombre(rs.getString("NOMBRE"));
                p.setSigla(rs.getString("SIGLA"));
                p.setActivo(rs.getBoolean("ACTIVO"));
            }
        }catch(SQLException e){
            throw new DataException(" AdminPais -> getPaisDefault() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return p;
    }
    
    @Override
    public void updatePais(Pais p) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE PAIS SET NOMBRE = '"+p.getNombre().toUpperCase();
            query+= "', SIGLA = '"+p.getSigla().toUpperCase();
            query+= "' WHERE IDPAIS="+p.getIdPais();
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminPais -> updatePais(Pais p) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public void deletePais(int idPais) throws DataException {
        PreparedStatement preSt = null;
        String query = "DELETE FROM PAIS WHERE IDPAIS="+idPais;
        try{
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            // ahora eliminamos sus dependencia :)
            query = "DELETE FROM CIUDAD WHERE IDPAIS="+idPais;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            query = "DELETE FROM PROVINCIA WHERE IDPAIS="+idPais;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException("Error! en AdminPais -> deletePais(idPais) e = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public boolean isUseInDetalleEst(int idPais) throws DataException{
        boolean res = false;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IDDETALLE_ESTUDIANTE";
        query += " FROM DETALLE_ESTUDIANTE";
        query += " WHERE IDPAIS_ACTUAL = "+idPais+" OR IDPAIS_NAC = "+idPais;
        query += " LIMIT 1";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next())
                res = true;
        }catch(SQLException e){
            throw new DataException(" AdminPais -> isUseInDetalleEst() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    public void darBaja(int idPais) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE PAIS SET ACTIVO = 0 WHERE IDPAIS="+idPais;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            
            query = "UPDATE CIUDAD SET ACTIVO = 0 WHERE IDPAIS="+idPais;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            
            query = "UPDATE PROVINCIA SET ACTIVO = 0 WHERE IDPAIS="+idPais;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminPais -> darBaja(int idPais) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void darAlta(int idPais) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE PAIS SET ACTIVO = 1 WHERE IDPAIS="+idPais;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            
            query = "UPDATE CIUDAD SET ACTIVO = 1 WHERE IDPAIS="+idPais;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            
            query = "UPDATE PROVINCIA SET ACTIVO = 1 WHERE IDPAIS="+idPais;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminPais -> darAlta(int idPais) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }    
}
